package tech.aistar.util;

import tech.aistar.util.tmp.IPreparedCallback;
import tech.aistar.util.tmp.IResultSetCallback;

import java.io.Serializable;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 本类用来演示: 模板设计模式 - 回调接口
 *
 * @author: success
 * @date: 2022/8/11 2:02 下午
 */
@SuppressWarnings("all")
public class JdbcTemplate {
    public static final String TABLE_PREFIX = "jdbc_";

    //DML
    public static int executeUpdate(IPreparedCallback pcb){
        Connection conn = null;
        PreparedStatement pst = null;
        int count = 0;
        try {
            conn = JdbcUtil.getConnection();
            //特殊的地方 - 个性的地方
            pst = pcb.execute(conn);
            count = pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.close(conn,pst);
        }
        return count;
    }

    //DQL
    public static Object executeQuery(IPreparedCallback pcb, IResultSetCallback rcb){
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        Object obj = null;
        try {
            conn = JdbcUtil.getConnection();
            //特殊的地方 - 个性的地方
            pst = pcb.execute(conn);
            rs = pst.executeQuery();
            obj = rcb.execute(rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.close(conn,pst);
        }
        return obj;
    }

    //全部关于反射封装... - DML操作 - 通用的
    public static int executeDML(String sql,Object... args){
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = JdbcUtil.getConnection();
            pst = conn.prepareStatement(sql);

            if(null!=args && args.length>0){
                //有参数,需要发送参数.
                for (int i = 0; i < args.length; i++) {
                    pst.setObject(i+1,args[i]);
                }
            }
            return pst.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.close(conn,pst);
        }
        return 0;
    }

    //通用的DQL - 返回单个对象
    //前提: 持久层框架遵守的一些规范 - orm思想
    // 表名(允许前缀)   类名
    // 列名    -      属性名
    // (蛇形)  -      (小驼峰)
    public static <T> T queryOne(Class<?> c,String sql,Object... args){
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        T t = null;
        try {
            conn = JdbcUtil.getConnection();
            pst = conn.prepareStatement(sql);

            //是否发送参数
            if(args != null & args.length>0){
                for (int i = 0; i < args.length; i++) {
                    pst.setObject(i+1,args[i]);
                }
            }
            rs = pst.executeQuery();

            //获取结果集元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //得到查询的列数
            int cols = rsmd.getColumnCount();

            //单个对象
            if(rs.next()){
                //反射调用构造方法
                t = (T) c.newInstance();
                //取cols个列
                for (int i = 1; i <= cols; i++) {
                    //通过结果集对象,得到列的名称
                    String colName = rsmd.getColumnName(i);

                    //此处列采用蛇形
                    if(colName.contains("_")){
                        //蛇形的=>小驼峰
                        colName = snake_tuo(colName);
                    }

                    //orm规范 列名应该和表名保持一致
                    //根据属性名来获取指定的该属性对应的Field对象
                    Field f = c.getDeclaredField(colName);
                    f.setAccessible(true);

                    //反射置值
                    f.set(t,rs.getObject(i));
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(conn,pst,rs);
        }
        return t;
    }

    //通用的查询多个
    public static <T> List<T> queryList(Class<?> c, String sql, Object... args){
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<T> list = new ArrayList<>();
        try {
            conn = JdbcUtil.getConnection();
            pst = conn.prepareStatement(sql);

            //是否发送参数
            if(args != null & args.length>0){
                for (int i = 0; i < args.length; i++) {
                    pst.setObject(i+1,args[i]);
                }
            }
            rs = pst.executeQuery();

            //获取结果集元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //得到查询的列数
            int cols = rsmd.getColumnCount();

            //单个对象
            while(rs.next()){
                //反射调用构造方法
                T t = (T) c.newInstance();
                //取cols个列
                for (int i = 1; i <= cols; i++) {
                    //通过结果集对象,得到列的名称
                    String colName = rsmd.getColumnName(i);

                    //此处列采用蛇形
                    if(colName.contains("_")){
                    //蛇形的=>小驼峰
                        colName = snake_tuo(colName);
                    }

                    //orm规范 列名应该和表名保持一致
                    //根据属性名来获取指定的该属性对应的Field对象
                    Field f = c.getDeclaredField(colName);
                    f.setAccessible(true);

                    //反射置值
                    f.set(t,rs.getObject(i));
                }
                list.add(t);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(conn,pst,rs);
        }
        return list;
    }

    //针对crud分别设置不同的处理方法
    //规范 - ORM思想
    public static <T> int save(T t){
        Class<?> c = t.getClass();
        //类的简称
        String className = c.getSimpleName();
        //表名 - 配置前缀
        String tableName = TABLE_PREFIX + className.toLowerCase();

        //定义一个StringBuilder
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("insert into "+tableName+" values(");

        //获取对象中的属性的个数
        Field[] fields = c.getDeclaredFields();
        if(fields!=null && fields.length>0){
            for (int i = 0; i < fields.length; i++) {
                if(i!=fields.length-1){
                    sqlBuilder.append("?,");
                }else{
                    sqlBuilder.append("?");
                }
            }
        }

        sqlBuilder.append(")");

        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = JdbcUtil.getConnection();
            pst = conn.prepareStatement(sqlBuilder.toString());

            if(fields!=null && fields.length>0){
                for (int i = 0; i < fields.length; i++) {
                   //反射拿到属性的值
                    fields[i].setAccessible(true);

                    //发送参数
                    pst.setObject(i+1,fields[i].get(t));
                }
            }
            return pst.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(conn,pst);
        }

        return 0;
    }

    public static String snake_tuo(String s1){
        StringBuilder s = new StringBuilder(s1);
        for ( int i = 0;i < s.length();i++ ) {
            if (s.charAt( i ) == '_'){
                s.replace( i , i + 2,String.valueOf( Character.toUpperCase(s.charAt( i+1 )) ) );
            }
        }
        return s.toString();
    }

    //针对getById
    public static <T> T getById(Class<?> c, Serializable id){
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        T t = null;
        try {
            conn = JdbcUtil.getConnection();
            String sql = "select * from jdbc_"+c.getSimpleName().toLowerCase()+" where id=?";
            pst = conn.prepareStatement(sql);

            //是否发送参数
            pst.setObject(1,id);
            rs = pst.executeQuery();

            //获取结果集元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //得到查询的列数
            int cols = rsmd.getColumnCount();

            //单个对象
            if(rs.next()){
                //反射调用构造方法
                t = (T) c.newInstance();
                //取cols个列
                for (int i = 1; i <= cols; i++) {
                    //通过结果集对象,得到列的名称
                    String colName = rsmd.getColumnName(i);

                    //此处列采用蛇形
                    if(colName.contains("_")){
                        //蛇形的=>小驼峰
                        colName = snake_tuo(colName);
                    }

                    //orm规范 列名应该和表名保持一致
                    //根据属性名来获取指定的该属性对应的Field对象
                    Field f = c.getDeclaredField(colName);
                    f.setAccessible(true);

                    //反射置值
                    f.set(t,rs.getObject(i));
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(conn,pst,rs);
        }
        return t;
    }

    //针对getAll
    public static <T> List<T> getAll(Class<?> c){
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<T> list = new ArrayList<>();
        try {
            conn = PoolUtil.getConnection();
            //System.out.println(conn.getClass());
            String sql = "select * from jdbc_"+c.getSimpleName().toLowerCase();
            pst = conn.prepareStatement(sql);

            //是否发送参数
            rs = pst.executeQuery();

            //获取结果集元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //得到查询的列数
            int cols = rsmd.getColumnCount();
            
            while(rs.next()){
                //反射调用构造方法
                T t = (T) c.newInstance();
                //取cols个列
                for (int i = 1; i <= cols; i++) {
                    //通过结果集对象,得到列的名称
                    String colName = rsmd.getColumnName(i);

                    //此处列采用蛇形
                    if(colName.contains("_")){
                        //蛇形的=>小驼峰
                        colName = snake_tuo(colName);
                    }

                    //orm规范 列名应该和表名保持一致
                    //根据属性名来获取指定的该属性对应的Field对象
                    Field f = c.getDeclaredField(colName);
                    f.setAccessible(true);

                    //反射置值
                    f.set(t,rs.getObject(i));
                }
                list.add(t);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(conn,pst,rs);
        }
        return list;
    }

    //针对delById
    public static int delById(Class<?> c,Serializable id){
        return executeUpdate(conn->{
            String sql = "delete from jdbc_"+c.getSimpleName().toLowerCase()+" where id=?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setObject(1,id);
            return pst;
        });
    }
}
